﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Proy200Millas_BE;

namespace Proy200Millas_ADO
{
    public class ProductoADO
    {
        ConexionADO MiConexion = new ConexionADO();
        SqlConnection cnx = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader dtr = default(SqlDataReader);
        Boolean vexito = false;

        public DataTable ListarProducto()
        {
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_ListarProductos";
                SqlDataAdapter miada = default(SqlDataAdapter);
                miada = new SqlDataAdapter(cmd);
                miada.Fill(dts, "Productos");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return dts.Tables["Productos"];
        }

        public bool InsertarProducto(ProductoBE BE)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_InsertarProducto";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vdes_pro", SqlDbType.VarChar, 25));
                cmd.Parameters["@vdes_pro"].Value = BE.Des_pro;
                cmd.Parameters.Add(new SqlParameter("@vtip_pro", SqlDbType.Char, 10));
                cmd.Parameters["@vtip_pro"].Value = BE.Tip_pro;
                cmd.Parameters.Add(new SqlParameter("@vcod_um", SqlDbType.Char,3));
                cmd.Parameters["@vcod_um"].Value = BE.Cod_um;
                cmd.Parameters.Add(new SqlParameter("@vpre_pro", SqlDbType.Money));
                cmd.Parameters["@vpre_pro"].Value = BE.Pre_pro;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                // No olvidarse!!!!!!
                cmd.Parameters.Clear();
            }
            return vexito;
        }

        public bool ActualizarProducto(ProductoBE BE)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_ActualizarProducto";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vcod_pro", SqlDbType.Char, 3));
                cmd.Parameters["@vcod_pro"].Value = BE.Cod_pro;
                cmd.Parameters.Add(new SqlParameter("@vdes_pro", SqlDbType.VarChar, 25));
                cmd.Parameters["@vdes_pro"].Value = BE.Des_pro;
                cmd.Parameters.Add(new SqlParameter("@vtip_pro", SqlDbType.Char, 10));
                cmd.Parameters["@vtip_pro"].Value = BE.Tip_pro;
                cmd.Parameters.Add(new SqlParameter("@vcod_um", SqlDbType.Char, 3));
                cmd.Parameters["@vcod_um"].Value = BE.Cod_um;
                cmd.Parameters.Add(new SqlParameter("@vpre_pro", SqlDbType.Money));
                cmd.Parameters["@vpre_pro"].Value = BE.Pre_pro;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                // No olvidarse!!!!!!
                cmd.Parameters.Clear();
            }
            return vexito;

        }


        public bool EliminarProducto(String vcod)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_EliminarProducto";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vcod_pro", SqlDbType.Char, 3));
                cmd.Parameters["@vcod_pro"].Value = vcod;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                // No olvidarse!!!!!!
                cmd.Parameters.Clear();
            }
            return vexito;

        }

        public ProductoBE ConsultarProducto(string vcod)
        {
            ProductoBE prodBE = new ProductoBE();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_ConsultarProducto";
                //Agregamos el parametro para el SP
                cmd.Parameters.Add(new SqlParameter("@vcod_pro", SqlDbType.Char, 3));
                cmd.Parameters["@vcod_pro"].Value = vcod;
                cnx.Open();
                dtr = cmd.ExecuteReader();
                if (dtr.HasRows == true)
                {
                    dtr.Read();
                    var _miprod = prodBE;
                    _miprod.Cod_pro = dtr.GetValue(dtr.GetOrdinal("cod_pro")).ToString();
                    _miprod.Des_pro = dtr.GetValue(dtr.GetOrdinal("des_pro")).ToString();
                    _miprod.Tip_pro = dtr.GetValue(dtr.GetOrdinal("tip_pro")).ToString();
                    _miprod.Cod_um = dtr.GetValue(dtr.GetOrdinal("cod_um")).ToString();
                    _miprod.Pre_pro = Convert.ToSingle(dtr.GetValue(dtr.GetOrdinal("pre_pro")));
                    dtr.Close();
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return prodBE;
        }

    }
}
